/*
 * Description:	数据库相关操作
 * Date:		2003.10.20
 * @author		张彦
 *@modify 巢庆丰
 */

package com.snailct.bpp.commons.utils;

import org.apache.log4j.Logger;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class DBManager implements  java.io.Serializable {
	private static final Logger logger = Logger.getLogger(DBManager.class);

	// 取默认数据库连接
	/**
	 * @return 此方法重新编写，不需更新服务器， 为方便Junit 测试 重新编写 add by xujh 2008-4-17
	 */
	public Connection getConnection(boolean isHis) throws Exception {
		Connection con = null;
		try {
			if (isHis) {
				con = getConnection("jdbc/btdb2");
			} else {
				con = getConnection("jdbc/btdb1");
			}
		} catch (Exception ex) {
			logger.error("数据源获取失败，获取测试系统数据源", ex);
		}
		return con;
	}

	public static Connection getConnection() throws Exception {
		Connection con = null;
		try {
			con = getConnection("jdbc/btdb1");
		} catch (Exception ex) {
			logger.error("数据源jdbc/btdb1获取失败，获取测试系统数据源", ex);
			throw ex;
		}
		return con;
	}

	/**
	 * 获取数据库连接
	 * @param jndi
	 * @return
	 * @throws Exception
	 * Modify by Michael Pan 20120925: 去掉成员变量，解决在多线程环境中可能出现的问题
	 */
	public static Connection getConnection(String jndi) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/bpp?useUnicode=true&characterEncoding\\=utf8&autoReconnect\\=true", "root", "1234567");
		return conn;
	}

	/**
	 * 返回连接指定的数据库类型<br/>
	 * @param cn
	 * @return 如果是Sqlserver数据库则返回1，如果是oracle或db2数据库则返回2
	 * @throws Exception 如果数据库是除sqlserver,oracle,db2外的其它类型，则抛出不支持的异常
	 * Modify By Michael Pan 20120926 修改成每次重新获取，避免“串号”，即获取值与实际连接对应的值不一致的情况出现。
	 */
	public static int getDBType(Connection cn) throws Exception {
		String dbName = cn.getMetaData().getDatabaseProductName();
		if (dbName.equals("Microsoft SQL Server")) {
			return 1;
		} else if (dbName.equals("Oracle") || dbName.startsWith("DB2")) {
			return 2;
		}

		throw new Exception(
				"对不起，目前本系统只能在Microsoft SQL Server和Oracle数据库上运行。");
	}


	/**
	 * 根据单位选择连接 corpId -> accboks_id -> jnid
	 *
	 * @param corpId
	 * @return Connection
	 * @throws Exception
	 */
	public static Connection getConnection(int corpId) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String jnid = "";
		// corp_id -> accboks_id
		String sSql = "select accbooks_id from bt_corp where id=?";
		try {
			con = getConnection();
			ps = con.prepareStatement(sSql);
			ps.setInt(1, corpId);
			rs = ps.executeQuery();
			// accboks_id -> jnid
			jnid = String.valueOf(rs.getInt("accbooks_id"));
		} catch (Exception ex) {
			logger.error("获取部门连接时出错", ex);
		} finally {
			releaseDBSource(rs, ps, con);
		}
		// 取数据库连接
		if ("".equals(jnid) || jnid == null)
			throw new Exception();
		else
			con = getConnection(jnid);
		return con;
	}

	public static Connection getConnectionOfErp() throws Exception {
		return getConnection("3");
	}

	private static int ORCL_DB_VERSION; // oracle版本

	public static int getOraclDBVersion() {
		if (ORCL_DB_VERSION == 0) {
			ORCL_DB_VERSION = getOrclDBVersion();
		}
		return ORCL_DB_VERSION;
	}

	/**
	 * @作者：黄茜
	 * @时间：2011.7.13
	 * @功能：区分oracle的版本
	 * @return
	 */
	private static int getOrclDBVersion() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int v_count = -1;
		String sql = "select count(*) as v_count from v$version where rownum=1 and (instr(banner,'10')>0 or instr(banner,'11')>0)";
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				v_count = Integer.parseInt(String.valueOf(rs.getInt(1)));
			}
		} catch (Exception ex) {
			logger.error("查询ORACLE数据库版本时出错", ex);
		} finally {
			releaseDBSource(rs, ps, con);
		}
		return v_count;
	}

	/**
	 * 附加数据库连接信息配置地址
	 */
	private static final String PROPERTIES_FILE_PATH = DBManager.class
			.getResource("/").getPath() + "/jdbc.properties";

	/**
	 * 获取中间库的链接，久其对接中间库链接获取
	 * @return
	 */
	public static Connection getZJKConnection() {
		Properties p = new Properties();
		File file = new File(PROPERTIES_FILE_PATH);
		try {
			FileInputStream in = new FileInputStream(file); // 构造文件的输入流
			p.load(in); // 读入属性
			in.close();
		} catch (IOException e) {
			logger.error("获取数据库配置出错", e);
			return null;
		}

		String driverName = p.getProperty("jdbc.driverClassName");
		String url = p.getProperty("jdbc.url");
		String user = p.getProperty("jdbc.username");
		String pass = p.getProperty("jdbc.password");
		try {
			Class.forName(driverName);
			return DriverManager.getConnection(url, user, pass);
		} catch (Exception ex) {
			logger.error("数据源获取失败，获取测试系统数据源", ex);
			return null;
		}
	}

	/**
	 * 释放连接数据库的资源
	 *
	 * @param rs
	 * @param st
	 * @param con
	 */
	public static void releaseDBSource(ResultSet rs, Statement st,
									   Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				logger.error("关闭数据集时出错", e);
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				logger.error("关闭数据库会话时出错", e);
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				logger.error("关闭连接时出错", e);
			}
		}
	}


	/**
	 * rpt1104/src/main/java/com/byttersoft/rpt1104/util/DBManager.java合并过来
	 * 关闭数据连接
	 *
	 * @param con
	 * @param ps
	 * @param rs
	 */
	public static void freeConn(Connection con, PreparedStatement ps, ResultSet rs) {
		if(null != rs){
			try {
				rs.close();
			} catch(SQLException e) {
				e.printStackTrace();
				logger.error("关闭数据集时出错", e);
			}
		}

		if(null != ps){
			try {
				ps.close();
			} catch(SQLException e) {
				e.printStackTrace();
				logger.error("关闭数据库会话时出错", e);
			}
		}

		if(con != null){
			try {
				if(!con.isClosed()){
					con.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
				logger.error("关闭连接时出错", e);
			}
		}
	}

	//释放连接数据库的资源 fanxianshi 20140107
	public static void close(ResultSet rs,Statement stmt,Connection conn){

		try{if(rs!=null)rs.close();}catch (Exception e){}
		try{if(stmt!=null)stmt.close();}catch (Exception e){}
		try{if(conn!=null)conn.close();}catch (Exception e){}
	}



}	
